﻿using BW.WMS.WareHouseManage;
using BW.WMS.WareHouseStatistics.Page;
using BW.WMS.WareHouseStatistics.SignalR;
using BW.WMS.WareHouseStatistics.Statistics;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Volo.Abp.Application.Dtos;
using Volo.Abp.Application.Services;
using Volo.Abp.Domain.Repositories;
using Volo.Abp.EventBus;
using Volo.Abp.EventBus.Local;
using OfficeOpenXml;

namespace BW.WMS.WareHouseStatistics
{
    public class StatisticsAppService : ApplicationService, IStatisticsAppService
    {
        private readonly IRepository<StockManagementModel, Guid> _StockManagementRepository;
        private readonly IRepository<Warehouse, Guid> _WarehouseRepository;
        private readonly ILocalEventBus _localEventBus;

        public StatisticsAppService(
            IRepository<StockManagementModel, Guid> stockManagementRepository,
            IRepository<Warehouse, Guid> warehouseRepository,
            ILocalEventBus localEventBus)
        {
            _localEventBus = localEventBus;
            _StockManagementRepository = stockManagementRepository;
            _WarehouseRepository = warehouseRepository;
        }
        /// <summary>
        /// 导出
        /// </summary>
        /// <param name="filter"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public async Task<byte[]> ExportDataAsync(ExportFilterDto filter)
        {
            var stockquery = await _StockManagementRepository.GetQueryableAsync();
            var housequery = await _WarehouseRepository.GetQueryableAsync();
            stockquery = stockquery.Where(x => !x.IsDeleted);

            // 先应用所有过滤条件到 stockquery
            if (!string.IsNullOrEmpty(filter.Stockcode))
            {
                stockquery = stockquery.Where(x => x.Stockcode.Contains(filter.Stockcode));
            }
            if (filter.Warehouseid != null)
            {
                stockquery = stockquery.Where(x => x.Warehouseid == filter.Warehouseid);
            }
            if (!string.IsNullOrEmpty(filter.Stocktype))
            {
                stockquery = stockquery.Where(x => x.Stocktype == filter.Stocktype);
            }
            if (filter.Stocktime != null)
            {
                var inputDate = filter.Stocktime.Value.Date;
                stockquery = stockquery.Where(x => x.Stocktime.Date == inputDate);
            }
            if (filter.CreationTime != null)
            {
                var inputDate = filter.CreationTime.Value.Date;
                stockquery = stockquery.Where(x => x.CreationTime.Date == inputDate);
            }

            // 创建两表连查查询
            var query = from stock in stockquery
                        join house in housequery on stock.Warehouseid equals house.Id into houseJoin
                        from house in houseJoin.DefaultIfEmpty()
                        select new StockStatisticsDto
                        {
                            Stockcode = stock.Stockcode,
                            Stocktime = stock.Stocktime,
                            Stocktype = stock.Stocktype,
                            Warehouseid = stock.Warehouseid,
                            HouseName = house != null ? house.Name : null,
                            Stocknumber = stock.Stocknumber,
                            Nostocknumber = stock.Nostocknumber,
                            Allocationnumber = stock.Allocationnumber,
                            Stockmoney = stock.Stockmoney,
                            Agent = stock.Agent,
                            Creator = stock.Creator,
                            CreationTime = stock.CreationTime,
                        };

            var data = await query.ToListAsync();

            // 使用 EPPlus 生成 Excel 文件
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial; // 设置许可证上下文

            using (var package = new ExcelPackage())
            {
                var worksheet = package.Workbook.Worksheets.Add("库存统计");

                // 设置标题行
                worksheet.Cells[1, 1].Value = "入库单号";
                worksheet.Cells[1, 2].Value = "入库时间";
                worksheet.Cells[1, 3].Value = "入库类型";
                worksheet.Cells[1, 4].Value = "仓库名称";
                worksheet.Cells[1, 5].Value = "入库数量";
                worksheet.Cells[1, 6].Value = "未入库数量";
                worksheet.Cells[1, 7].Value = "调拨数量";
                worksheet.Cells[1, 8].Value = "入库金额";
                worksheet.Cells[1, 9].Value = "经办人";
                worksheet.Cells[1, 10].Value = "制单人";
                worksheet.Cells[1, 11].Value = "制单时间";

                // 设置标题行样式
                using (var range = worksheet.Cells[1, 1, 1, 11])
                {
                    range.Style.Font.Bold = true;
                    range.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
                    range.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.LightGray);
                }

                // 填充数据
                for (int i = 0; i < data.Count; i++)
                {
                    var item = data[i];
                    int row = i + 2; // 从第2行开始（第1行是标题）

                    worksheet.Cells[row, 1].Value = item.Stockcode ?? "";
                    worksheet.Cells[row, 2].Value = item.Stocktime.ToString("yyyy-MM-dd");
                    worksheet.Cells[row, 3].Value = item.Stocktype ?? "";
                    worksheet.Cells[row, 4].Value = item.HouseName ?? "";
                    worksheet.Cells[row, 5].Value = item.Stocknumber ?? 0;
                    worksheet.Cells[row, 6].Value = item.Nostocknumber ?? 0;
                    worksheet.Cells[row, 7].Value = item.Allocationnumber ?? 0;
                    worksheet.Cells[row, 8].Value = item.Stockmoney ?? 0;
                    worksheet.Cells[row, 9].Value = item.Agent ?? "";
                    worksheet.Cells[row, 10].Value = item.Creator ?? "";
                    worksheet.Cells[row, 11].Value = item.CreationTime?.ToString("yyyy-MM-dd") ?? "";
                }

                // 自动调整列宽
                worksheet.Cells.AutoFitColumns();

                // 返回 Excel 文件的字节数组
                return package.GetAsByteArray();
            }
        }

        /// <summary>
        /// 显示
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<Page.PagedResultDto<StockStatisticsDto>> GetStatisticsListAsync(StaticsticsGetList input)
        {
            var stockquery = await _StockManagementRepository.GetQueryableAsync();
            var housequery = await _WarehouseRepository.GetQueryableAsync();
            stockquery = stockquery.Where(x => !x.IsDeleted);

            // 先应用所有过滤条件到 stockquery
            if (!string.IsNullOrEmpty(input.Stockcode))
            {
                stockquery = stockquery.Where(x => x.Stockcode.Contains(input.Stockcode));
            }
            if (input.Warehouseid != null)
            {
                stockquery = stockquery.Where(x => x.Warehouseid == input.Warehouseid);
            }
            if (!string.IsNullOrEmpty(input.Stocktype))
            {
                stockquery = stockquery.Where(x => x.Stocktype == input.Stocktype);
            }
            if (input.Stocktime != null)
            {
                var inputDate = input.Stocktime.Value.Date;
                stockquery = stockquery.Where(x => x.Stocktime.Date == inputDate);
            }
            if (input.CreationTime != null)
            {
                var inputDate = input.CreationTime.Value.Date;
                stockquery = stockquery.Where(x => x.CreationTime.Date == inputDate);
            }

            // 创建两表连查查询
            var query = from stock in stockquery
                        join house in housequery on stock.Warehouseid equals house.Id into houseJoin
                        from house in houseJoin.DefaultIfEmpty()
                        select new StockStatisticsDto
                        {
                            Stockcode = stock.Stockcode,
                            Stocktime = stock.Stocktime,
                            Stocktype = stock.Stocktype,
                            Warehouseid = stock.Warehouseid,
                            HouseName = house != null ? house.Name : null,
                            Stocknumber = stock.Stocknumber,
                            Nostocknumber = stock.Nostocknumber,
                            Allocationnumber = stock.Allocationnumber,
                            Stockmoney = stock.Stockmoney,
                            Agent = stock.Agent,
                            Creator = stock.Creator,
                            CreationTime = stock.CreationTime,
                        };


            var Stocknumber = await query.SumAsync(x => x.Stocknumber); //入库数量
            var Nostocknumber = await query.SumAsync(x => x.Nostocknumber);  //未入库数量
            var Allocationnumber = await query.SumAsync(x => x.Allocationnumber);//调拨数量
            var Stockmoney = await query.SumAsync(x => x.Stockmoney); //入库金额


            // 发布统计通知事件
            await _localEventBus.PublishAsync(new StatisticsNotificationEventData("有新的统计数据更新！"));

            // 获取总记录数
            var totalCount = await AsyncExecuter.CountAsync(query);

            // 应用分页
            var pagedQuery = query.OrderByDescending(x => x.CreationTime)
                .Skip((input.PageIndex - 1) * input.PageSize)
                .Take(input.PageSize);

            // 执行查询获取数据
            var items = await AsyncExecuter.ToListAsync(pagedQuery);

            return Page.PagedResultDto<StockStatisticsDto>.Create(items, totalCount, input.PageIndex, input.PageSize);
        }

    }
}